﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using ERP_RORZE_SYSTEM;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace ERP_RORZE_DLL.Business
{
    public partial class frm_ChangeQuantity_ImportQuotation : Form
    {
        private INISettings mySetting;
        private SqlConnection myConnection;        

        public frm_ChangeQuantity_ImportQuotation()
        {
            InitializeComponent();
            mySetting = new INISettings();
            myConnection = new SqlConnection(mySetting.SQLConnectionString);
            LoadData();
            ChangeQuantity.CellValueChanged += ChangeQuantity_CellValueChanged;
        }

        void ChangeQuantity_CellValueChanged(object sender, DevExpress.XtraGrid.Views.Base.CellValueChangedEventArgs e)
        {
            ChangeQuantity.PostEditor();
            ChangeQuantity.UpdateCurrentRow();
        }

        ///////////////////////////////////
        /// Load Data From Local Database
        ///////////////////////////////////
        private void LoadData()
        {
            DataSet sourceDataSet = new DataSet();
            INISettings mySettings = new INISettings();
            SQLManagement mySQLManagement = new SQLManagement();
            mySQLManagement.SQLConnectionString = mySettings.SQLConnectionString;
            string sql = "SELECT t1.[ID], t1.[Date], t1.[PartNo], t1.[Quantity], t1.[Price], t5.[UnitNameEN], t1.[Old],"
                + " t4.[SupplierName], t3.[EnglishName], t6.[CurrencyName], t1.[Note]"
                + " FROM [241_ImportQuotation] AS t1 LEFT OUTER JOIN"
                + " [222_Part] AS t2 ON t1.[PartNo]=t2.[PartNo] LEFT OUTER JOIN"
                + " [222_Maker] AS t3 ON t3.[MakerID]=t2.[MakerID] LEFT OUTER JOIN"
                + " [222_Supplier] AS t4 ON t4.[SupplierID]=t1.[SupplierID] LEFT OUTER JOIN"
                + " [222_Unit] AS t5 ON t5.[UnitID]=t2.[UnitID] LEFT OUTER JOIN"
                + " [222_Currency] AS t6 ON t6.[CurrencyID]=t1.[CurrencyID]"
                + " WHERE t1.[Old] = 0 AND t1.[ChangeQuantity] = 1"
                + " ORDER BY t1.[PartNo], t1.[Quantity]";
            sourceDataSet = mySQLManagement.SQL_Query(sql);
            if (sourceDataSet != null)
            {
                gridChangeQuantity.DataSource = sourceDataSet.Tables[0];                
            }
            else
            {
                MessageBox.Show("There is no data for Import Quotation");
                DataTable newTable = new DataTable();
                newTable.Columns.Add("ID", typeof(int));
                newTable.Columns.Add("Date", typeof(string));
                newTable.Columns.Add("PartNo", typeof(int));
                newTable.Columns.Add("Quantity", typeof(string));
                newTable.Columns.Add("Price", typeof(string));
                newTable.Columns.Add("Unit", typeof(string));
                newTable.Columns.Add("Old", typeof(bool));
                newTable.Columns.Add("Supplier", typeof(string));
                newTable.Columns.Add("Maker", typeof(string));
                newTable.Columns.Add("Currency", typeof(string));
                newTable.Columns.Add("Note", typeof(string));
                gridChangeQuantity.DataSource = newTable;
            }
            ChangeQuantity.Columns[0].Caption = "ID";
            ChangeQuantity.Columns[1].Caption = "Date";
            ChangeQuantity.Columns[2].Caption = "Part No";
            ChangeQuantity.Columns[3].Caption = "Quantity";
            ChangeQuantity.Columns[4].Caption = "Price";
            ChangeQuantity.Columns[5].Caption = "Unit";
            ChangeQuantity.Columns[6].Caption = "Delete";
            ChangeQuantity.Columns[7].Caption = "Supplier";
            ChangeQuantity.Columns[8].Caption = "Maker";            
            ChangeQuantity.Columns[9].Caption = "Currency";
            ChangeQuantity.Columns[10].Caption = "Note";
            ChangeQuantity.Columns[0].Visible = false;
            ChangeQuantity.BestFitColumns();
            ChangeQuantity.Columns[0].OptionsColumn.AllowEdit = false;
            ChangeQuantity.Columns[1].OptionsColumn.AllowEdit = false;
            ChangeQuantity.Columns[2].OptionsColumn.AllowEdit = false;
            ChangeQuantity.Columns[3].OptionsColumn.AllowEdit = false;
            ChangeQuantity.Columns[4].OptionsColumn.AllowEdit = false;
            ChangeQuantity.Columns[5].OptionsColumn.AllowEdit = false;            
            ChangeQuantity.Columns[7].OptionsColumn.AllowEdit = false;
            ChangeQuantity.Columns[8].OptionsColumn.AllowEdit = false;
            ChangeQuantity.Columns[9].OptionsColumn.AllowEdit = false;
            ChangeQuantity.Columns[10].OptionsColumn.AllowEdit = false;
        }

        private void buttonCancel_Click(object sender, EventArgs e)
        {
            this.Dispose();
        }

        private void buttonUpdate_Click(object sender, EventArgs e)
        {            
            for (int i = 0; i < ChangeQuantity.DataRowCount; i++)
            {
                DataRow row = ChangeQuantity.GetDataRow(i);
                UpdateChange(row);
            }            
            MessageBox.Show("Update successfully!");
            LoadData();
        }

        private void UpdateChange(DataRow change_row)
        {

            try
            {
                myConnection.Open();
                SqlCommand cmd_sql = myConnection.CreateCommand();
                if ((bool)change_row[6])
                {
                    cmd_sql.CommandText = "UPDATE [241_ImportQuotation] SET ChangeQuantity=0, Old=1 WHERE id=" + change_row[0].ToString();
                }
                else
                {
                    cmd_sql.CommandText = "UPDATE [241_ImportQuotation] SET ChangeQuantity=0, Old=0 WHERE id=" + change_row[0].ToString();
                }
                cmd_sql.ExecuteNonQuery();
                cmd_sql.Dispose();
                myConnection.Close();
            }
            catch (Exception ex)
            {
                if (myConnection.State == ConnectionState.Open)
                {
                    myConnection.Close();
                }
                MessageBox.Show(ex.Message);
                //MessageBox.Show("Cannot update database");
            }
        }       
    }
}
